package com.b2c.repository;

import com.b2c.entity.result.PagingResponse;
import com.b2c.entity.result.EnumResultVo;
import com.b2c.entity.result.ResultVo;
import com.b2c.entity.zbj.LiveDataEntity;
import com.b2c.entity.zbj.LiveExpensesEntity;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

@Repository
public class LiveDataRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    Logger log = LoggerFactory.getLogger(LiveDataRepository.class);

    /**
     * 添加数据
     * @param en
     * @return 返回自增ID
     */
    @Transactional
    public ResultVo<Long> addLiveData(LiveDataEntity en) {
        var isExist = jdbcTemplate.queryForObject("SELECT COUNT(0) FROM "+Tables.ErpLiveData+" WHERE authorAccount=? AND liveStartTime=? ",Integer.class,en.getAuthorAccount(),en.getLiveStartTime());
        if(isExist > 0 ) return new ResultVo<>(EnumResultVo.NotFound, "已存在");

        StringBuilder sql =new StringBuilder();
        sql.append("INSERT INTO ").append(Tables.ErpLiveData);
        sql.append("(");
        sql.append("authorImg,");
        sql.append("authorName,");
        sql.append("authorAccount,");
        sql.append("liveStartTime,");
        sql.append("liveEndTime,");
        sql.append("liveTime,");
        sql.append("showUV,");
        sql.append("showPV,");
        sql.append("UV,");
        sql.append("PV,");
        sql.append("PCU,");
        sql.append("ACU,");
        sql.append("TS,");
        sql.append("comments,");
        sql.append("xjt,");
        sql.append("xzfs,");
        sql.append("qgfs,");
        sql.append("kbfszb,");
        sql.append("goodsCount,");
        sql.append("goodsShow,");
        sql.append("goodsClick,");
        sql.append("goodsShowTotal,");
        sql.append("goodsClickTotal,");
        sql.append("orderCount,");
        sql.append("orderAmount,");
        sql.append("orderGoodsCount,");
        sql.append("orderUser,");
        sql.append("orderRefundCount,");
        sql.append("orderRefundAmount,");
        sql.append("orderRefundUser,");
        sql.append("commission,");
        sql.append("goodsClickRate,");
        sql.append("goodsClickRate2,");
        sql.append("goodsClickTransactionRate,");
        sql.append("goodsClickTransactionRate2,");
        sql.append("viewTransactionRate,");
        sql.append("viewTransactionRate2,");
        sql.append("preOrderCount,");
        sql.append("preOrderAmount");
        sql.append(",liveDate)");

        sql.append(" VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");

        try {
            //插入数据库
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, en.getAuthorImg());
                    ps.setString(2, en.getAuthorName());
                    ps.setString(3, en.getAuthorAccount());
                    ps.setString(4, en.getLiveStartTime());
                    ps.setString(5, en.getLiveEndTime());
                    ps.setFloat(6, en.getLiveTime());
                    ps.setLong(7, en.getShowUV());
                    ps.setLong(8, en.getShowPV());
                    ps.setLong(9, en.getUV());
                    ps.setLong(10, en.getPV());
                    ps.setLong(11, en.getPCU());
                    ps.setLong(12, en.getACU());
                    ps.setFloat(13, en.getTS());
                    ps.setLong(14, en.getComments());
                    ps.setLong(15, en.getXjt());
                    ps.setLong(16, en.getXzfs());
                    ps.setLong(17, en.getQgfs());
                    ps.setDouble(18, en.getKbfszb());
                    ps.setLong(19, en.getGoodsCount());
                    ps.setLong(20, en.getGoodsShow());
                    ps.setLong(21, en.getGoodsClick());
                    ps.setLong(22, en.getGoodsShowTotal());
                    ps.setLong(23, en.getGoodsClickTotal());
                    ps.setLong(24, en.getOrderCount());
                    ps.setDouble(25, en.getOrderAmount());
                    ps.setLong(26, en.getOrderGoodsCount());
                    ps.setLong(27, en.getOrderUser());
                    ps.setLong(28, en.getOrderRefundCount());
                    ps.setDouble(29, en.getOrderRefundAmount());
                    ps.setLong(30, en.getOrderRefundUser());
                    ps.setDouble(31, en.getCommission());
                    ps.setDouble(32, en.getGoodsClickRate());
                    ps.setDouble(33, en.getGoodsClickRate2());
                    ps.setDouble(34, en.getGoodsClickTransactionRate());
                    ps.setDouble(35, en.getGoodsClickTransactionRate2());
                    ps.setDouble(36, en.getViewTransactionRate());
                    ps.setDouble(37, en.getViewTransactionRate2());
                    ps.setLong(38, en.getPreOrderCount());
                    ps.setDouble(39, en.getPreOrderAmount());
                    ps.setString(40,en.getLiveStartTime());
                    return ps;
                }
            }, keyHolder);

            Long id = keyHolder.getKey().longValue();
            return new ResultVo<>(EnumResultVo.SUCCESS, id);
        }catch (Exception e){
            return new ResultVo<>(EnumResultVo.Fail, "系统异常"+e.getMessage());
        }
    }


    /**
     * 分页获取数据
     * @param pageIndex
     * @param pageSize
     * @param authorAccount
     * @return
     */
    @Transactional
    public PagingResponse<LiveDataEntity> getList(Integer pageIndex, Integer pageSize, String authorAccount) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS *,IFNULL((SELECT SUM(expensesTotal) FROM erp_live_expenses WHERE liveId = erp_live_data.id),0) AS expensesTotal FROM ");
        sb.append(Tables.ErpLiveData);
        sb.append(" WHERE status=1 ");

        List<Object> params = new ArrayList<>();
        if (!StringUtils.isEmpty(authorAccount)) {
            sb.append(" AND authorAccount =  ? ");
            params.add(authorAccount);
        }

        sb.append(" ORDER BY liveDate DESC,id DESC");

        if(pageSize != null){
            sb.append(" LIMIT ?,? ");
            params.add((pageIndex - 1) * pageSize);
            params.add(pageSize);
        }
        var lists = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(LiveDataEntity.class), params.toArray(new Object[params.size()]));

        if(pageSize == null){
            pageSize = lists.size();
        }
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), lists);
    }

    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    /**
     * 获取直播营销费用
     * @param liveId
     * @return
     */
    public LiveExpensesEntity getLiveExpensesByLiveId(Long liveId) {
        try {
            String sql = "SELECT * FROM " + Tables.ErpLiveExpenses + " WHERE liveId=? LIMIT 1 ";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(LiveExpensesEntity.class), liveId);
        }catch (Exception e) {
            return new LiveExpensesEntity();
        }
    }

    /**
     * 编辑直播营销费用
     * @param entity
     */
    public void editLiveExpenses(LiveExpensesEntity entity) {
        try {
            String sql = "SELECT * FROM " + Tables.ErpLiveExpenses + " WHERE liveId=? LIMIT 1 ";
            var e = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(LiveExpensesEntity.class), entity.getLiveId());
            //修改
            String updSQL = "UPDATE "+ Tables.ErpLiveExpenses+" SET expenses1=?,expenses2=?,expenses3=?,expenses4=?,expenses5=?,expensesTotal=? WHERE id=?";
            jdbcTemplate.update(updSQL,entity.getExpenses1(),entity.getExpenses2(),entity.getExpenses3(),entity.getExpenses4(),entity.getExpenses5(),entity.getExpensesTotal(),e.getId());
        }catch (Exception e) {
            //新增
            String sql = "INSERT INTO "+Tables.ErpLiveExpenses+" (liveId,expenses1,expenses2,expenses3,expenses4,expenses5,expensesTotal) VALUE (?,?,?,?,?,?,?)";
            jdbcTemplate.update(sql,entity.getLiveId(),entity.getExpenses1(),entity.getExpenses2(),entity.getExpenses3(),entity.getExpenses4(),entity.getExpenses5(),entity.getExpensesTotal());
        }
    }


    public void addLiveReplayFile(Long liveId, String filePath) {
        String sql = "UPDATE "+Tables.ErpLiveData +" SET repalyFilePath=? WHERE id=?";
        jdbcTemplate.update(sql,filePath,liveId);
    }

    public LiveDataEntity getLiveById(Long liveId){
        try {
            String sql = "SELECT * FROM " + Tables.ErpLiveData + " WHERE id=? LIMIT 1 ";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(LiveDataEntity.class), liveId);
        }catch (Exception e) {
            return null;
        }
    }

    public void delLiveData(Long liveId) {
        jdbcTemplate.update("UPDATE "+Tables.ErpLiveData +" SET status=0 WHERE id=?",liveId);
    }
}
